package com.yqtsoft.utils.excel.core;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.SimpleDateFormat;

/**
 * @description: excel工具类
 * @auther: CrazyShaQiuShi
 * @email 3105334046@qq.com
 * @date: 2018-12-11 9:27
 * @version: 1.0
 */
public class ExcelTool {

    /**
     * 当前行数
     */
    private static int num = 0;

    private static HSSFWorkbook wb;

    private static HSSFCell cell;

    private static HSSFRow row;


    public static HSSFWorkbook createWorkbook() {
        wb = new HSSFWorkbook();
        return wb;
    }

    /**
     * 创建一个sheet
     *
     * @param sheetname
     * @return HSSFSheet 返回类型
     * @throws
     */
    public static HSSFSheet createSheet(int ii, String sheetname) {
        // 创建excel工作簿
        HSSFSheet sheet = wb.createSheet();
        wb.setSheetName(ii, sheetname);
        return sheet;
    }

    /**
     * 创建一行
     *
     * @param sheet
     * @return HSSFRow 返回类型
     * @throws
     */
    public static HSSFRow createRow(HSSFSheet sheet) {
        row = sheet.createRow(num);
        num++;
        return row;
    }

    /**
     * 创建一格
     *
     * @param row
     * @param col
     * @return HSSFCell 返回类型
     * @throws
     */
    public static HSSFCell createCell(HSSFRow row, int col) {
        // 创建cell
        cell = row.createCell(col);
        return cell;
    }

    /**
     * 获取合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);

                    return getCellValue(fCell);
                }
            }
        }

        return null;
    }

    /**
     * 获取非合并单元格的值
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getCellValueByRowColumn(Sheet sheet, int row, int column) {
        Row fRow = sheet.getRow(row);
        Cell fCell = fRow.getCell(column);
        return getCellValue(fCell);
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 获取唯一标识
     *
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    @Deprecated
    public static String getUUID(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return String.format("%s=%s", lastRow, column);
                }
            }
        }
        return String.format("%s=%s", row, column);
    }

    /**
     * 获取唯一标识
     *
     * @param sheet
     * @param row
     * @param column
     * @return 返回当前唯一标识和是否是合并单元格
     */
    public static ExcelUUID getExcelUUID(Sheet sheet, int row, int column, boolean ensureMerge) {
        ExcelUUID excelUUID = new ExcelUUID();
        //不确定时序号判断获取
        if (!ensureMerge) {
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        excelUUID.setUuid(String.format("%s=%s", lastRow, column));
                        excelUUID.setMergedRegion(true);
                        Row fRow = sheet.getRow(firstRow);
                        Cell fCell = fRow.getCell(firstColumn);
                        //设置值
                        excelUUID.setValue(getCellValue(fCell));
                        return excelUUID;
                    }
                }
            }
        }
        excelUUID.setUuid(String.format("%s=%s", row, column));
        excelUUID.setMergedRegion(false);
        excelUUID.setValue(getCellValueByRowColumn(sheet, row, column));
        return excelUUID;
    }

    /**
     * 获取单元格值
     *
     * @param sheet
     * @param row
     * @param column
     * @param ensureMerge 是否确定是合并单元格 默认不确定
     * @return 返回单元格值包含的对象信息
     */
    public static ExcelTuple getExcelTuple(Sheet sheet, int row, int column, boolean ensureMerge) {
        ExcelTuple excelTuple = new ExcelTuple();
        //不确定时现判断是否时合并单元格，若是直接返回值
        if (!ensureMerge) {
            int sheetMergeCount = sheet.getNumMergedRegions();
            for (int i = 0; i < sheetMergeCount; i++) {
                CellRangeAddress ca = sheet.getMergedRegion(i);
                int firstColumn = ca.getFirstColumn();
                int lastColumn = ca.getLastColumn();
                int firstRow = ca.getFirstRow();
                int lastRow = ca.getLastRow();
                if (row >= firstRow && row <= lastRow) {
                    if (column >= firstColumn && column <= lastColumn) {
                        excelTuple.setMergedRegion(true);
                        Row fRow = sheet.getRow(firstRow);
                        Cell fCell = fRow.getCell(firstColumn);
                        //设置值
                        excelTuple.setValue(getCellValue(fCell));
                        return excelTuple;
                    }
                }
            }
        }
        excelTuple.setMergedRegion(false);
        excelTuple.setValue(getCellValueByRowColumn(sheet, row, column));
        return excelTuple;
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            //数值类型
            case NUMERIC:
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).trim();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    return dataFormatter.formatCellValue(cell);
                }
            case BLANK:
                return "";
            case ERROR:
                return "非法值";
            default:
                return "未知数据类型";
        }
    }

    public static int getNum() {
        return num;
    }

    public static void setNum(int num) {
        ExcelTool.num = num;
    }

    public static HSSFWorkbook getWb() {
        return wb;
    }

    public static void setWb(HSSFWorkbook wb) {
        ExcelTool.wb = wb;
    }

    public static HSSFCell getCell() {
        return cell;
    }

    public static void setCell(HSSFCell cell) {
        ExcelTool.cell = cell;
    }

    public static HSSFRow getRow() {
        return row;
    }

    public static void setRow(HSSFRow row) {
        ExcelTool.row = row;
    }


}
